package com.swe.chums.dao.impl;

import com.swe.chums.dao.FeedbackDao;
import com.swe.chums.dao.model.Feedback;
import com.swe.chums.dao.model.FeedbackPk;
import com.swe.chums.dao.exceptions.FeedbackDaoException;
import java.util.Date;
import java.util.List;
import javax.sql.DataSource;
import java.sql.ResultSet;
import java.sql.SQLException;
import org.springframework.jdbc.core.simple.SimpleJdbcTemplate;
import org.springframework.jdbc.core.simple.ParameterizedRowMapper;
import org.springframework.transaction.annotation.Transactional;

public class FeedbackDaoImpl extends AbstractDAO implements ParameterizedRowMapper<Feedback>, FeedbackDao
{
	protected SimpleJdbcTemplate jdbcTemplate;

	protected DataSource dataSource;

	/**
	 * Method 'setDataSource'
	 * 
	 * @param dataSource
	 */
	public void setDataSource(DataSource dataSource)
	{
		this.dataSource = dataSource;
		jdbcTemplate = new SimpleJdbcTemplate(dataSource);
	}

	/**
	 * Method 'insert'
	 * 
	 * @param dto
	 * @return FeedbackPk
	 */
	@Transactional
	public FeedbackPk insert(Feedback dto)
	{
		jdbcTemplate.update("INSERT INTO " + getTableName() + " ( userID, text, date, processed ) VALUES ( ?, ?, ?, ? )",dto.getUserID(),dto.isTextNull()?null:dto.getText(),dto.getDate(),dto.isProcessedNull()?null:dto.getProcessed());
		FeedbackPk pk = new FeedbackPk();
		pk.setId( jdbcTemplate.queryForInt("select last_insert_id()") );
		return pk;
	}

	/** 
	 * Updates a single row in the feedback table.
	 */
	@Transactional
	public void update(FeedbackPk pk, Feedback dto) throws FeedbackDaoException
	{
		jdbcTemplate.update("UPDATE " + getTableName() + " SET id = ?, userID = ?, text = ?, date = ?, processed = ? WHERE id = ?",dto.getId(),dto.getUserID(),dto.getText(),dto.getDate(),dto.getProcessed(),pk.getId());
	}

	/** 
	 * Deletes a single row in the feedback table.
	 */
	@Transactional
	public void delete(FeedbackPk pk) throws FeedbackDaoException
	{
		jdbcTemplate.update("DELETE FROM " + getTableName() + " WHERE id = ?",pk.getId());
	}

	/**
	 * Method 'mapRow'
	 * 
	 * @param rs
	 * @param row
	 * @throws SQLException
	 * @return Feedback
	 */
	public Feedback mapRow(ResultSet rs, int row) throws SQLException
	{
		Feedback dto = new Feedback();
		dto.setId( rs.getInt( 1 ) );
		dto.setUserID( rs.getInt( 2 ) );
		dto.setText( rs.getString( 3 ) );
		if (rs.wasNull()) {
			dto.setTextNull( true );
		}
		
		dto.setDate( rs.getTimestamp(4 ) );
		dto.setProcessed( rs.getInt( 5 ) );
		if (rs.wasNull()) {
			dto.setProcessedNull( true );
		}
		
		return dto;
	}

	/**
	 * Method 'getTableName'
	 * 
	 * @return String
	 */
	public String getTableName()
	{
		return "spring2013g3.feedback";
	}

	/** 
	 * Returns all rows from the feedback table that match the criteria 'id = :id'.
	 */
	@Transactional
	public Feedback findByPrimaryKey(int id) throws FeedbackDaoException
	{
		try {
			List<Feedback> list = jdbcTemplate.query("SELECT id, userID, text, date, processed FROM " + getTableName() + " WHERE id = ?", this,id);
			return list.size() == 0 ? null : list.get(0);
		}
		catch (Exception e) {
			throw new FeedbackDaoException("Query failed", e);
		}
		
	}

	/** 
	 * Returns all rows from the feedback table that match the criteria ''.
	 */
	@Transactional
	public List<Feedback> findAll() throws FeedbackDaoException
	{
		try {
			return jdbcTemplate.query("SELECT id, userID, text, date, processed FROM " + getTableName() + " ORDER BY id", this);
		}
		catch (Exception e) {
			throw new FeedbackDaoException("Query failed", e);
		}
		
	}

	/** 
	 * Returns all rows from the feedback table that match the criteria 'userID = :userID'.
	 */
	@Transactional
	public List<Feedback> findByUser(int userID) throws FeedbackDaoException
	{
		try {
			return jdbcTemplate.query("SELECT id, userID, text, date, processed FROM " + getTableName() + " WHERE userID = ?", this,userID);
		}
		catch (Exception e) {
			throw new FeedbackDaoException("Query failed", e);
		}
		
	}

	/** 
	 * Returns all rows from the feedback table that match the criteria 'id = :id'.
	 */
	@Transactional
	public List<Feedback> findWhereIdEquals(int id) throws FeedbackDaoException
	{
		try {
			return jdbcTemplate.query("SELECT id, userID, text, date, processed FROM " + getTableName() + " WHERE id = ? ORDER BY id", this,id);
		}
		catch (Exception e) {
			throw new FeedbackDaoException("Query failed", e);
		}
		
	}

	/** 
	 * Returns all rows from the feedback table that match the criteria 'userID = :userID'.
	 */
	@Transactional
	public List<Feedback> findWhereUserIDEquals(int userID) throws FeedbackDaoException
	{
		try {
			return jdbcTemplate.query("SELECT id, userID, text, date, processed FROM " + getTableName() + " WHERE userID = ? ORDER BY userID", this,userID);
		}
		catch (Exception e) {
			throw new FeedbackDaoException("Query failed", e);
		}
		
	}

	/** 
	 * Returns all rows from the feedback table that match the criteria 'text = :text'.
	 */
	@Transactional
	public List<Feedback> findWhereTextEquals(String text) throws FeedbackDaoException
	{
		try {
			return jdbcTemplate.query("SELECT id, userID, text, date, processed FROM " + getTableName() + " WHERE text = ? ORDER BY text", this,text);
		}
		catch (Exception e) {
			throw new FeedbackDaoException("Query failed", e);
		}
		
	}

	/** 
	 * Returns all rows from the feedback table that match the criteria 'date = :date'.
	 */
	@Transactional
	public List<Feedback> findWhereDateEquals(Date date) throws FeedbackDaoException
	{
		try {
			return jdbcTemplate.query("SELECT id, userID, text, date, processed FROM " + getTableName() + " WHERE date = ? ORDER BY date", this,date);
		}
		catch (Exception e) {
			throw new FeedbackDaoException("Query failed", e);
		}
		
	}

	/** 
	 * Returns all rows from the feedback table that match the criteria 'processed = :processed'.
	 */
	@Transactional
	public List<Feedback> findWhereProcessedEquals(int processed) throws FeedbackDaoException
	{
		try {
			return jdbcTemplate.query("SELECT id, userID, text, date, processed FROM " + getTableName() + " WHERE processed = ? ORDER BY processed", this,processed);
		}
		catch (Exception e) {
			throw new FeedbackDaoException("Query failed", e);
		}
		
	}

	/** 
	 * Returns the rows from the feedback table that matches the specified primary-key value.
	 */
	public Feedback findByPrimaryKey(FeedbackPk pk) throws FeedbackDaoException
	{
		return findByPrimaryKey( pk.getId() );
	}

}
